# -*- coding: utf-8 -*-
# This code written by ChanGOGOGO
"""
樊登话单月结(客户侧)
"""
import pymysql
from util import (
    logging,
    write_excel,
    data2beautify,
    date_slice,
    get_month_time_scale,
    query_es,
    check_str_is_num,
    ms2min,
    prefix_contain_str,
    db_ip,
    db_port,
    db_username,
    db_password,
    excel_save_dir,
    first_second_last_part,
)


class ExportMonthlyBillsByClientFD:
    def __init__(self, ui_data):
        self.data_date = ui_data["data_date"]
        self.business = ui_data.get("business")
        self.data_type = ui_data.get("data_type")
        company_id = ui_data.get("company_id")
        self.db_name = f"yicall_{company_id}"
        self.table_index = ui_data.get("business_id")
        self.conn = pymysql.connect(
            host=db_ip,
            port=db_port,
            user=db_username,
            passwd=db_password,
            charset="utf8",
        )
        self.cursor = self.conn.cursor()
        self.scene_info = self.query_scene()
        self.task_info = self.query_task()
        self.close_db()
        self.es_index = (
            f"{self.db_name}_{self.table_index}_callout_session_ach_{self.data_date}"
        )

    def query_task(self):
        """查询任务信息"""
        sql = f"""select task_id,task_name from {self.db_name}.b_{self.table_index}_callout_task;"""
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        task_info = {}
        if len(result) > 0:
            task_info = {task_id: task_name for task_id, task_name in result}
        logging.info(f"查询到{self.business}的任务信息：\n{data2beautify(task_info)}")
        return task_info

    def query_scene(self):
        """查询场景信息"""
        sql = f"""select scene_id,scene_name from {self.db_name}.b_{self.table_index}_call_scene
        order by scene_id desc;"""
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        scene_info = {}
        if len(result) > 0:
            scene_info = {scene_id: scene_name for scene_id, scene_name in result}
        return scene_info

    def close_db(self):
        self.cursor.close()
        self.conn.close()

    def query_callout_data(self):
        """查询呼出通话记录信息"""
        es_data = list()
        fields = ["taskId", "calloutDialNumber", "dialogDuration"]
        # 以“天”为单位查询es数据，避免scroll超时
        month_start_time, month_end_time = get_month_time_scale(self.data_date)
        date_slice_result = date_slice(month_start_time, month_end_time)
        for everyday in date_slice_result:
            day_start_time = everyday[0]
            day_end_time = everyday[1]
            body = {
                "query": {
                    "bool": {
                        "must": [
                            {"term": {"calloutConnectStatus": 100}},
                            {
                                "range": {
                                    "calloutFinishTime": {
                                        "gte": day_start_time,
                                        "lte": day_end_time,
                                    }
                                }
                            },
                        ]
                    }
                }
            }
            one_day_es_data = query_es(self.es_index, body, fields)
            es_data += one_day_es_data
        return es_data

    def export(self):
        """导出通话时长月结账单数据"""
        callout_data = self.query_callout_data()
        data_num = len(callout_data)
        # ------------------------将数据库查询出来的数据进行归类---------------------
        bills = {"测试任务": {}, "樊登正式任务": {}}  # 樊登测试任务
        if data_num == 0:
            msg = (
                f"<font color=green>无法导出“{self.business}”的【{self.data_type}】，"
                f"原因：\n在{self.data_date}没有呼出数据！</font>"
            )
            return {"code": 1, "excel_names": None, "msg": msg}
        else:
            logging.info(
                f"查询到{self.business}在{self.data_date}的{self.data_type}数据共{data_num}条。"
            )
            for x in callout_data:
                # try:
                task_id = x.get("taskId")
                line_number = x.get("calloutDialNumber")
                dialog_duration = int(x.get("dialogDuration"))
                task_name = self.task_info.get(int(task_id))
                dialog_duration_min = ms2min(
                    dialog_duration
                )  # 通话时长由毫秒转换成分钟，向上取整
                logging.info(task_name, line_number, dialog_duration_min)
                task_name_split = task_name.split("_")
                task_name_prefix = task_name_split[0]
                task_name_ending = task_name_split[-1]
                """
                1、任务名下划线数量小于2，直接判定为测试任务
                2、任务名下划线数量大于等于2的话，看看任务名结尾是不是数字001/002这种结尾，数字结尾也认定为测试任务
                3、任务名以“樊登”开头也判定为测试任务
                """
                underscore_num = task_name.count("_")
                if underscore_num >= 2:
                    # 拆分后的任务名最后部分纯数字，或者任务名开头为“樊登”，都归类为其他任务(测试任务)
                    if check_str_is_num(
                        task_name_ending
                    ) or task_name_prefix.startswith("樊登"):
                        find_test_task_exists = bills.get("测试任务").get(
                            task_name, "inexistence"
                        )  # 任务名key是否存在
                        if find_test_task_exists != "inexistence":  # 存在任务名称key
                            find_line_number = (
                                bills.get("测试任务")
                                .get(task_name)
                                .get(line_number, "inexistence")
                            )
                            if find_line_number != "inexistence":  # 存在主叫号码key
                                bills["测试任务"][task_name][
                                    line_number
                                ] += dialog_duration_min
                            else:
                                bills["测试任务"][task_name][
                                    line_number
                                ] = dialog_duration_min
                        else:
                            bills["测试任务"][task_name] = {
                                line_number: dialog_duration_min
                            }
                    else:
                        # 任务名以config.yaml文件中指定的对象开头，取第一部分、第二部分、最后部分、主叫号码为维度统计通话月结账单
                        if prefix_contain_str(task_name_prefix, first_second_last_part):
                            find_first_part_exists = bills.get("樊登正式任务").get(
                                task_name_prefix, "inexistence"
                            )
                            if (
                                find_first_part_exists != "inexistence"
                            ):  # 第一部分key存在
                                find_second_part_exists = (
                                    bills.get("樊登正式任务")
                                    .get(task_name_prefix)
                                    .get(task_name_split[1], "inexistence")
                                )
                                if (
                                    find_second_part_exists != "inexistence"
                                ):  # 第二部分key存在
                                    find_last_part_exists = (
                                        bills.get("樊登正式任务")
                                        .get(task_name_prefix)
                                        .get(task_name_split[1])
                                        .get(task_name_ending, "inexistence")
                                    )
                                    if (
                                        find_last_part_exists != "inexistence"
                                    ):  # 最后一部分key存在
                                        find_line_number = (
                                            bills.get("樊登正式任务")
                                            .get(task_name_prefix)
                                            .get(task_name_split[1])
                                            .get(task_name_ending)
                                            .get(line_number, "inexistence")
                                        )
                                        if find_line_number != "inexistence":
                                            bills["樊登正式任务"][task_name_prefix][
                                                task_name_split[1]
                                            ][task_name_ending][
                                                line_number
                                            ] += dialog_duration_min
                                        else:
                                            bills["樊登正式任务"][task_name_prefix][
                                                task_name_split[1]
                                            ][task_name_ending][
                                                line_number
                                            ] = dialog_duration_min
                                    else:  # 最后一部分key不存在
                                        bills["樊登正式任务"][task_name_prefix][
                                            task_name_split[1]
                                        ][task_name_ending] = {
                                            line_number: dialog_duration_min
                                        }
                                else:  # 第二部分key不存在
                                    bills["樊登正式任务"][task_name_prefix][
                                        task_name_split[1]
                                    ] = {
                                        task_name_ending: {
                                            line_number: dialog_duration_min
                                        }
                                    }
                            else:  # 第一部分key不存在
                                bills["樊登正式任务"][task_name_prefix] = {
                                    task_name_split[1]: {
                                        task_name_ending: {
                                            line_number: dialog_duration_min
                                        }
                                    }
                                }
                        else:  # 取任务名第一部分、最后部分、主叫号码为维度统计通话月结账单
                            find_first_part_exists = bills.get("樊登正式任务").get(
                                task_name_prefix, "inexistence"
                            )
                            if (
                                find_first_part_exists != "inexistence"
                            ):  # 第一部分key存在
                                find_last_part_exists = (
                                    bills.get("樊登正式任务")
                                    .get(task_name_prefix)
                                    .get(task_name_ending, "inexistence")
                                )
                                if (
                                    find_last_part_exists != "inexistence"
                                ):  # 最后部分key存在
                                    find_line_number = (
                                        bills.get("樊登正式任务")
                                        .get(task_name_prefix)
                                        .get(task_name_ending)
                                        .get(line_number, "inexistence")
                                    )
                                    if (
                                        find_line_number != "inexistence"
                                    ):  # 主叫号码key存在
                                        bills["樊登正式任务"][task_name_prefix][
                                            task_name_ending
                                        ][line_number] += dialog_duration_min
                                    else:
                                        bills["樊登正式任务"][task_name_prefix][
                                            task_name_ending
                                        ][line_number] = dialog_duration_min
                                else:  # 最后部分不存在
                                    bills["樊登正式任务"][task_name_prefix][
                                        task_name_ending
                                    ] = {line_number: dialog_duration_min}
                            else:  # 第一部分不存在
                                bills["樊登正式任务"][task_name_prefix] = {
                                    task_name_ending: {line_number: dialog_duration_min}
                                }
                else:  # 樊登下非正式拨打任务
                    find_test_task_exists = bills.get("测试任务").get(
                        task_name, "inexistence"
                    )
                    if find_test_task_exists != "inexistence":  # 任务名key存在
                        find_line_number = (
                            bills.get("测试任务")
                            .get(task_name)
                            .get(line_number, "inexistence")
                        )
                        if (
                            find_line_number != "inexistence"
                        ):  # 任务在某日期某主叫号码存在拨打
                            bills["测试任务"][task_name][
                                line_number
                            ] += dialog_duration_min
                        else:  # 任务在某日期某主叫号码不存在拨打
                            bills["测试任务"][task_name][
                                line_number
                            ] = dialog_duration_min
                    else:  # 任务名key不存在
                        bills["测试任务"][task_name] = {
                            line_number: dialog_duration_min
                        }
                # except Exception as reason:
                #     logging.error(f'处理【{x}】数据报错：{reason}')
            logging.info(
                f"查询出{self.business}{self.data_type}归类后的账单：\n{data2beautify(bills)}"
            )
            # ------------------------将数据库查询出来的数据进行处理，然后写入excel---------------------------------
            all_sheet_infos = list()  # 所有sheet页数据
            t_task_data = bills.get("测试任务")  # 樊登其他任务数据
            if bool(t_task_data) is True:  # 如果存在数据
                t_task_excel_tile = ["任务名", "主叫号码", "通话时长(分钟)"]
                t_excel_sheet_name = "测试任务"
                t_task_excel_data = list()
                for t_task_name, t_line_number_info in t_task_data.items():
                    for (
                        t_line_number,
                        t_dialog_duration_min,
                    ) in t_line_number_info.items():
                        t_task_excel_data.append(
                            [t_task_name, t_line_number, t_dialog_duration_min]
                        )
                t_task_excel_data.append(
                    ["总计", "", f"=SUM(C2:C{len(t_task_excel_data) + 1})"]
                )  # 拨打时长汇总
                all_sheet_infos.append(
                    [t_excel_sheet_name, t_task_excel_tile, t_task_excel_data]
                )
            formal_task_data = bills.get("樊登正式任务")  # 樊登樊登正式任务数据
            if bool(formal_task_data) is True:  # 如果存在数据
                for f_data_attribution, f_data_info in formal_task_data.items():
                    if prefix_contain_str(f_data_attribution, first_second_last_part):
                        f_task_excel_title = [
                            "数据归属",
                            "城市",
                            "描述",
                            "主叫号码",
                            "通话时长",
                        ]
                        f_task_excel_sheet_name = f_data_attribution
                        f_task_excel_data = list()
                        for ff_channel_name, ff_channel_info in f_data_info.items():
                            for (
                                fff_desc,
                                fff_line_number_info,
                            ) in ff_channel_info.items():
                                for (
                                    fff_line_number,
                                    fff_dialog_duration_min,
                                ) in fff_line_number_info.items():
                                    f_task_excel_data.append(
                                        [
                                            f_data_attribution,
                                            ff_channel_name,
                                            fff_desc,
                                            fff_line_number,
                                            fff_dialog_duration_min,
                                        ]
                                    )
                        f_task_excel_data.append(
                            [
                                "总计",
                                "",
                                "",
                                "",
                                f"=SUM(E2:E{len(f_task_excel_data) + 1})",
                            ]
                        )
                        all_sheet_infos.append(
                            [
                                f_task_excel_sheet_name,
                                f_task_excel_title,
                                f_task_excel_data,
                            ]
                        )
                    else:
                        f_task_excel_title = [
                            "数据归属",
                            "描述信息",
                            "主叫号码",
                            "通话时长(分钟)",
                        ]
                        f_task_excel_sheet_name = f_data_attribution
                        f_task_excel_data = list()
                        for ff_desc_info, ff_line_number_info in f_data_info.items():
                            for (
                                ff_line_number,
                                ff_dialog_duration_min,
                            ) in ff_line_number_info.items():
                                f_task_excel_data.append(
                                    [
                                        f_data_attribution,
                                        ff_desc_info,
                                        ff_line_number,
                                        ff_dialog_duration_min,
                                    ]
                                )
                        f_task_excel_data.append(
                            ["总计", "", "", f"=SUM(D2:D{len(f_task_excel_data) + 1})"]
                        )
                        all_sheet_infos.append(
                            [
                                f_task_excel_sheet_name,
                                f_task_excel_title,
                                f_task_excel_data,
                            ]
                        )
            file_name = f"{self.business}_{self.data_type}_{self.data_date}"
            full_file_name = write_excel(all_sheet_infos, excel_save_dir, file_name)
            logging.info(f"数据处理完成，请于'{excel_save_dir}'查看文件。")
            return {"code": 0, "excel_names": [full_file_name], "msg": None}


if __name__ == "__main__":
    ...
